package com.RecipeBook.browse;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Set;

import com.RecipeBook.common.Ingredient;
import com.RecipeBook.common.Recipe;
import com.RecipeBook.common.RecipeStep;
import com.RecipeBook.main.R;

import android.app.ProgressDialog;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper{

	//The Android's default system path of your application database.
	private static String DB_PATH = "/data/data/com.RecipeBook.main/databases/";

	private static String DB_NAME = "rb";

	private SQLiteDatabase myDataBase; 
	int total = 21828;

	private final Context myContext;
	String query = null;

	String[] drop = {"DROP TABLE ingredient","DROP TABLE step","DROP TABLE ingredient_list","DROP TABLE recipes"};
	String createIng = "CREATE TABLE ingredient (_id INTEGER PRIMARY KEY, name TEXT, description TEXT);";
	String createFav = "CREATE TABLE favorites (recipe_id INTEGER);";
/*	String[] insertIng = {"INSERT INTO ingredient VALUES(1,'Water','Hydrogen 2 Oxygen');",
			"INSERT INTO ingredient VALUES(2,'Meatballs','Frozen, precooked meatballs.');",
			"INSERT INTO ingredient VALUES(3,'Noodles','Spaghetti');",
			"INSERT INTO ingredient VALUES(4,'Pasta Sauce',NULL);",
			"INSERT INTO ingredient VALUES(5,'Chicken','Preferably dead.');",
			"INSERT INTO ingredient VALUES(6,'Caesar Salad Mix',NULL);",
			"INSERT INTO ingredient VALUES(7,'Caesar Dressing',NULL);",
			"INSERT INTO ingredient VALUES(8,'Ranch Dressing',NULL);",
	"INSERT INTO ingredient VALUES(9,'Blue Cheese Dressing',NULL);"};*/
	String createIngList = "CREATE TABLE ingredient_list (recipe_id INTEGER, step_num NUMERIC, quantity NUMERIC, units TEXT, _id INTEGER PRIMARY KEY);";
/*	String[] insertIngList = {"INSERT INTO ingredient_list VALUES(1,1,8,'gallons',1);",
			"INSERT INTO ingredient_list VALUES(1,3,24,'meatballs',2);",
			"INSERT INTO ingredient_list VALUES(1,2,3,'noodles',3);",
			"INSERT INTO ingredient_list VALUES(1,3,1,'jar',4);",
			"INSERT INTO ingredient_list VALUES(2,1,1,'whole chicken',5);",
			"INSERT INTO ingredient_list VALUES(3,1,1,'bag',6);",
			"INSERT INTO ingredient_list VALUES(3,2,3,'squirts',7);",
			"INSERT INTO ingredient_list VALUES(4,3,1,'squirt',8);",
	"INSERT INTO ingredient_list VALUES(4,3,1,'squirt',9);"};*/
	//"Main Ingredient", "Cuisine", "Course", "Dish Type", "Dietary Considerations"
	String createRec = "CREATE TABLE recipes (_id INTEGER PRIMARY KEY, name TEXT, tags TEXT, rating NUMERIC, time NUMERIC, main_ing TEXT, cuisine TEXT, course TEXT, dish_type TEXT, dietary_cons TEXT, image_url TEXT);";
	String[] insertRec = {"INSERT INTO recipes VALUES(1,'Spaghetti and Meatballs','pasta italian maincourse',4,30,'Beef','Italian','Dinner','Pasta','','http://knoxify.com/wp-content/uploads/2010/08/spaghetti-meatballs.jpg');",
			"INSERT INTO recipes VALUES(2,'Chicken','poultry allamerican maincourse',3,60,'Chicken','All American','Dinner','Meat','','http://scienceblogs.com/gregladen/chicken.jpg');",
			"INSERT INTO recipes VALUES(3,'Caesar Salad','salad sidedish italian',1,10,'Vegetables','Italian','Appetizer','Salad','Vegetarian','http://www.proteinpower.com/drmd_blog/wp-content/uploads/2007/08/caesar-salad.jpg');",
	"INSERT INTO recipes VALUES(4,'Chicken Wings','sidedish allamerican',5,40,'Chicken','All American','Appetizer','Meat','','http://turbo.inquisitr.com/wp-content/2010/12/chicken-wing-new-years-appetizer.jpg');"};
	String createStep = "CREATE TABLE step (_id INTEGER, step_num NUMERIC, text TEXT);";
	String[] insertStep = {"INSERT INTO step VALUES(1,1,'Boil water.');",
			"INSERT INTO step VALUES(1,2,'Add noodles. Cook for 10 minutes or until they stick to the cieling.');",
			"INSERT INTO step VALUES(1,3,'Microwave meatballs, add to sauce and noodles when hot.');",
			"INSERT INTO step VALUES(2,1,'Defrost in microwave.');",
			"INSERT INTO step VALUES(2,2,'Serve when internal temperature is 165 degrees.');",
			"INSERT INTO step VALUES(3,1,'Open bag of salad mix.');",
			"INSERT INTO step VALUES(3,2,'Add dressing.');",
			"INSERT INTO step VALUES(4,1,'Order from pizzahut/wingstreet.');",
			"INSERT INTO step VALUES(4,2,'Answer door when they are delivered.');",
	"INSERT INTO step VALUES(4,3,'Keep warm in oven until ready to serve. Serve with ranch or blue cheese.');"};


	/**
	 * Constructor
	 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
	 * @param context
	 */
	public DataBaseHelper(Context context) {

		super(context, DB_NAME, null, 1);
		this.myContext = context;
	}	

	/**
	 * Creates a empty database on the system and rewrites it with your own database.
	 * */
	/*	public void createDataBase() throws IOException{

		boolean dbExist = checkDataBase();

		if(dbExist){
			//do nothing - database already exist
		}else{

			//By calling this method and empty database will be created into the default system path
			//of your application so we are gonna be able to overwrite that database with our database.
			this.getReadableDatabase();

			try {
				copyDataBase();

			} catch (IOException e) {

				throw new Error("Error copying database");

			}
		}

	}*/

	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.
	 * @return true if it exists, false if it doesn't
	 */
	/*private boolean checkDataBase(){

		SQLiteDatabase checkDB = null;

		try{
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

		}catch(SQLiteException e){

			//database does't exist yet.

		}

		if(checkDB != null){

			checkDB.close();

		}

		return checkDB != null ? true : false;
	}*/

	/**
	 * Copies your database from your local assets-folder to the just created empty database in the
	 * system folder, from where it can be accessed and handled.
	 * This is done by transfering bytestream.
	 * */
	/*	private void copyDataBase() throws IOException{

		File f = new File(DB_PATH);
		if(!f.exists())
		{
			f.mkdir();
		}

		//Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		//Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		//transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer))>0){
			myOutput.write(buffer, 0, length);
		}

		//Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}*/

	public void openDataBase() throws SQLException{

		//Open the database
		//String myPath = DB_PATH + DB_NAME;
		myDataBase = this.getWritableDatabase();
	}

	@Override
	public synchronized void close() {

		if(myDataBase != null)
			myDataBase.close();

		super.close();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}

	public ArrayList<Integer> getRecipePKs()
	{
		String[] columns = new String[]{"_id"};
		ArrayList<Integer> recipeList = new ArrayList<Integer>();

		//myDataBase.execSQL(insert);
		Cursor mCursor = myDataBase.query("recipes", columns, null, null, null, null, null);
		mCursor.moveToFirst();
		int r = mCursor.getCount();
		recipeList.add(mCursor.getInt(0));
		for(mCursor.moveToFirst(); mCursor.moveToNext(); mCursor.isAfterLast()) {
			// The Cursor is now set to the right position
			recipeList.add(mCursor.getInt(0));
		}
		return recipeList;
	}
	// Add your public helper methods to access and get content from the database.
	// You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
	// to you to create adapters for your views.

	public ArrayList<Integer> search(HashMap<String, String> queryData) 
	{
		final String[] columns = {"_id"};
		final ArrayList<Integer> recipeList = new ArrayList<Integer>();
		query = "";
		if(queryData.size() > 0)
		{
			query = "";
			Set<String> keys = queryData.keySet();
			for(String key:keys)
			{
				//main_ing TEXT, cuisine TEXT, course TEXT, dish_type TEXT, dietary_cons
				String new_key = "";
				if(key == "Main Ingredient")
					new_key = "main_ing";
				else if(key == "Dish Type")
					new_key = "dish_type";
				else if(key == "Dietary Considerations")
					new_key = "dietary_cons";
				else
					new_key = key.toLowerCase();

				query += new_key + " = '" + queryData.get(key) + "'";
				query += " and ";
			}
			query = query.substring(0, query.length() - 4);
		}
		Cursor mCursor = myDataBase.query("recipes", columns, query, null, null, null, null);
		//recipeList.add(mCursor.getInt(0));
		//int r = mCursor.getCount();
		mCursor.moveToFirst();
		recipeList.add(mCursor.getInt(0));
		for(mCursor.moveToFirst(); mCursor.moveToNext(); mCursor.isAfterLast()) {
			// The Cursor is now set to the right position
			recipeList.add(mCursor.getInt(0));
		}

		return recipeList;
	}

	public void prepareDB(ProgressDialog pd) 
	{
		try
		{myDataBase.execSQL(createFav);}
		catch(Exception e){}
		try
		{myDataBase.execSQL(createIng);	}
		catch(Exception e){
			System.out.println(e.toString());
		}
		try
		{myDataBase.execSQL(createStep);		}
		catch(Exception e){}
		try
		{	myDataBase.execSQL(createIngList);		}
		catch(Exception e){}
		try
		{		myDataBase.execSQL(createRec);		}
		catch(Exception e){	}

		// Cleaning up
		myDataBase.delete("recipes", null, null);
		myDataBase.delete("step", null, null);
		myDataBase.delete("ingredient_list", null, null);
		myDataBase.delete("ingredient", null, null);

		String qry1 = "SELECT name FROM ingredient";
		Cursor c1 = myDataBase.rawQuery(qry1,null);
		if(c1.getCount() < 7000)
		{
			myDataBase.execSQL(drop[0]);
			myDataBase.execSQL(createIng);
			try
			{
				ArrayList<String> ingInsert = new ArrayList<String>();
				ingInsert = getLines("i.txt");
				for(int x = 0; x < ingInsert.size(); x++)
				{
					myDataBase.execSQL(ingInsert.get(x));
					if((x%100)==0)
						pd.setProgress(x);
				}
			}
			catch(Exception e)
			{
				System.out.println(e.toString());
			}
		}
		int p = (int) (100*(((double)7185)/21828));
		pd.setProgress(7185);
		qry1 = "SELECT step_num FROM step";
		c1 = myDataBase.rawQuery(qry1,null);
		if(c1.getCount() < 6000)
		{
			myDataBase.execSQL(drop[1]);
			myDataBase.execSQL(createStep);
			try
			{
				ArrayList<String> ingInsert = new ArrayList<String>();
				ingInsert = getLines("s.txt");
				for(int x = 0; x < ingInsert.size(); x++)
				{
					myDataBase.execSQL(ingInsert.get(x));
					if((x%100)==0)
						pd.setProgress(7185+x);
				}
			}
			catch(Exception e)
			{
				System.out.println(e.toString());
			}
		}
		p = (int) (100*(((double)(6742+7185))/21828));
		pd.setProgress(6742+7185);

		qry1 = "SELECT step_num FROM ingredient_list";
		c1 = myDataBase.rawQuery(qry1,null);
		if(c1.getCount() < 7000)
		{
			myDataBase.execSQL(drop[2]);
			myDataBase.execSQL(createIngList);
			try
			{
				ArrayList<String> ingInsert = new ArrayList<String>();
				ingInsert = getLines("il.txt");
				for(int x = 0; x < ingInsert.size(); x++)
				{
					myDataBase.execSQL(ingInsert.get(x));
					if((x%100)==0)
						pd.setProgress(6742+7185+x);
				}
			}
			catch(Exception e)
			{
				System.out.println(e.toString());
			}
		}
		p = (int) (100*(((double)(6742+(2*7185)))/21828));
		pd.setProgress(6742+7185+7185);

		qry1 = "SELECT name FROM recipes";
		c1 = myDataBase.rawQuery(qry1,null);
		if(c1.getCount() < 500)
		{
			myDataBase.execSQL(drop[3]);
			myDataBase.execSQL(createRec);
			try
			{
				ArrayList<String> ingInsert = new ArrayList<String>();
				ingInsert = getLines("r.txt");
				for(int x = 0; x < ingInsert.size(); x++)
				{
					myDataBase.execSQL(ingInsert.get(x));
					if((x%100)==0)
						pd.setProgress(x+(6742+(2*7185)));
				}
			}
			catch(Exception e)
			{
				System.out.println(e.toString());
			}
		}
	}

	private ArrayList<String> getLines(String file) 
	{
		Context c = myContext;
		InputStream is = null;
		if(file == "i.txt")
			is = c.getResources().openRawResource(R.raw.i);
		if(file == "il.txt")
			is = c.getResources().openRawResource(R.raw.il);
		if(file == "r.txt")
			is = c.getResources().openRawResource(R.raw.r);
		if(file == "s.txt")
			is = c.getResources().openRawResource(R.raw.s);
		ArrayList<String> temp = new ArrayList<String>();
		/*FileInputStream in = null;
		try 
		{
			in = new FileInputStream(file);
		} catch (FileNotFoundException e) 
		{
			e.printStackTrace();
		}*/
		//DataInputStream input = new DataInputStream(in);
		BufferedReader d  = new BufferedReader(new InputStreamReader(is));
		String line = null;
		try {
			line = d.readLine();
			while(line != null)
			{
				temp.add(line);
				line = d.readLine();
			}
			d.close();
		} 
		catch (IOException e) {
			e.printStackTrace();
		}
		return temp;
	}


	public void clearFavorites()
	{
		myDataBase.delete("favorites", null, null);
	}


	public void addToFavorites(int recipeID)
	{
		String query = "SELECT * FROM favorites WHERE recipe_id = " + recipeID;
		Cursor mCursor = myDataBase.rawQuery(query, null);
		mCursor.moveToFirst();

		// Insert it
		if(mCursor.getCount() == 0){
			ContentValues values = new ContentValues();
			values.put("recipe_id", recipeID);
			myDataBase.insert("favorites", null, values);
		}	
	}

	public void removeFavorite(int recipeID)
	{
		myDataBase.delete("favorites", "recipe_id=?", new String[] {Integer.toString(recipeID)});
	}

	public ArrayList<Integer> getFavourites() 
	{
		ArrayList<Integer> recipeList = new ArrayList<Integer>();
		Cursor mCursor = myDataBase.query("favorites", null, null, null, null, null, null);

		for(mCursor.moveToFirst(); !mCursor.isAfterLast(); mCursor.moveToNext()) {
			recipeList.add(mCursor.getInt(0));
		}
		return recipeList;
	}

	public ArrayList<String> getNameFromID(ArrayList<Integer> recipeIDs) 
	{
		//String[] columns = new String[]{"name"};
		ArrayList<String> recipeList = new ArrayList<String>();
		String query = "SELECT NAME from recipes WHERE ";
		for(int i = 0; i < recipeIDs.size(); i++)
		{
			query = query + "_id = " + recipeIDs.get(i);
			if(i < (recipeIDs.size() - 1))
			{
				query += " or ";
			}
		}
		//myDataBase.execSQL(insert);
		Cursor mCursor = myDataBase.rawQuery(query, null);
		//mCursor.moveToFirst();
		//int r = mCursor.getCount();
		//recipeList.add(mCursor.getString(0));
		for(mCursor.moveToFirst(); mCursor.moveToNext(); mCursor.isAfterLast()) {
			// The Cursor is now set to the right position
			recipeList.add(mCursor.getString(0));
		}
		return recipeList;
	}

	public Recipe getRecipeFromId(int id) {
		String[] columns = new String[] {"name","rating", "time", "image_url"};
		Cursor mCursor = myDataBase.query("recipes", columns, "_id = '"+id+"'", null, null, null, null);
		mCursor.moveToFirst();
		String name = mCursor.getString(0);
		int rating = mCursor.getInt(1);
		int time = mCursor.getInt(2);
		String imageURL = mCursor.getString(3);

		columns = new String[] {"_id"};
		mCursor = myDataBase.query("ingredient_list", columns, "recipe_id = '"+id+"'", null, null, null, null);
		ArrayList<Ingredient> il = new ArrayList<Ingredient>();
		columns = new String[] {"name","description"};
		//for (mCursor.moveToFirst(); mCursor.moveToNext(); mCursor.isAfterLast()) {
		for (mCursor.moveToFirst(); !mCursor.isAfterLast(); mCursor.moveToNext()){
			Cursor nCursor = myDataBase.query("ingredient", columns, "_id = '"+mCursor.getInt(0)+"'", null, null, null, null);
			nCursor.moveToFirst();
			il.add(new Ingredient(nCursor.getString(0),nCursor.getString(1)));
		}

		columns = new String[] {"step_num","text"};
		mCursor = myDataBase.query("step", columns, "_id = '"+id+"'", null, null, null, null);
		ArrayList<RecipeStep> sl = new ArrayList<RecipeStep>();
		//mCursor.moveToFirst();
		//sl.add(new RecipeStep(mCursor.getInt(0), mCursor.getString(1)));
		for (mCursor.moveToFirst(); !mCursor.isAfterLast(); mCursor.moveToNext()) {
			sl.add(new RecipeStep(mCursor.getInt(0), mCursor.getString(1)));
		}

		return new Recipe(id, name, rating, time, il, sl, imageURL);
	}

}